package modele.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import db.MySQLSingleton;

import modele.beanEntity.Recipe;

public class RecipeMapper {
	
	private Connection db;
	
	public RecipeMapper() throws Exception{
		this.db=MySQLSingleton.getInstance();
	}
	
	public Recipe getRecipe(int id) throws SQLException{
		PreparedStatement ps =this.db.prepareStatement("SELECT * FROM receipes WHERE idreceipes= ?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			Recipe r =new Recipe(rs.getInt("idreceipes"), rs.getString("title"), rs.getString("sumup"), rs.getString("description"),
					rs.getString("imageref"), rs.getInt("nbofperson"), rs.getString("cooktype"), rs.getInt("cookexpertise"), 
					rs.getInt("preparationduration"));
			rs.close();
			ps.close();
			return r;
		}
		
		rs.close();
		ps.close();
		
		return null;
	}
	
	public List<Recipe> getRecipeList() throws SQLException{
		Statement s=this.db.createStatement();
		ResultSet rs = s.executeQuery("SELECT * FROM receipes");
		List<Recipe> l=new ArrayList<Recipe>();
		while(rs.next()){
			l.add(new Recipe(rs.getInt("idreceipes"), rs.getString("title"), rs.getString("sumup"), rs.getString("description"),
					rs.getString("imageref"), rs.getInt("nbofperson"), rs.getString("cooktype"), rs.getInt("cookexpertise"), 
					rs.getInt("preparationduration")));
		}
		rs.close();
		s.close();
		return l;
	}
	
	public boolean createRecipe(String title, String sumUp, String description,
			String imageRef, int nbPersons, String cookType,
			int cookExpertise, int preparationDuration) throws SQLException{
		
		PreparedStatement ps = this.db.prepareStatement("INSERT INTO receipes(title,sumup,description,imageref,nbofperson," +
				"cooktype,cookexpertise,preparationduration) VALUES (?,?,?,?,?,?,?,?);");
		ps.setString(1, title);
		ps.setString(2, sumUp);
		ps.setString(3, description);
		ps.setString(4, imageRef);
		ps.setInt(5, nbPersons);
		ps.setString(6, cookType);
		ps.setInt(7, cookExpertise);
		ps.setInt(8, preparationDuration);
		
		if(ps.executeUpdate()== 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
		
	}
	
	public boolean createRecipe(Recipe r) throws SQLException{
		return this.createRecipe(r.getTitle(),r.getSumUp(),r.getDescription(),r.getImageRef(),r.getNbPersons(),
				r.getCookType(),r.getCookExpertise(),r.getPreparationDuration());
	}
	
	public boolean updateRecipe(int id,String title, String sumUp, String description,
	String imageRef, int nbPersons, String cookType,
	int cookExpertise, int preparationDuration) throws SQLException{

		PreparedStatement ps = this.db.prepareStatement("UPDATE receipes SET title=?,sumup=?,description=?,imageref=?," +
				"nbofperson=?,cooktype=?,cookexpertise=?,preparationduration=? WHERE idreceipes=?;");
		ps.setString(1, title);
		ps.setString(2, sumUp);
		ps.setString(3, description);
		ps.setString(4, imageRef);
		ps.setInt(5, nbPersons);
		ps.setString(6, cookType);
		ps.setInt(7, cookExpertise);
		ps.setInt(8, preparationDuration);
		ps.setInt(9, id);
		if(ps.executeUpdate()== 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
	
	}
	
	public boolean updateRecipe(Recipe r) throws SQLException{
		return this.updateRecipe(r.getId(),r.getTitle(),r.getSumUp(),r.getDescription(),r.getImageRef(),r.getNbPersons(),
				r.getCookType(),r.getCookExpertise(),r.getPreparationDuration());
	}
	
	public boolean deleteRecipe(int id) throws SQLException{
		PreparedStatement ps = this.db.prepareStatement("DELETE FROM receipes WHERE idreceipes= ?");
		ps.setInt(1,id);
		if(ps.executeUpdate() == 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
	}
	
	public boolean deleteRecipe(Recipe r) throws SQLException{
		return this.deleteRecipe(r.getId());
	}
	
	public List<Recipe> searchRecipe(int preparationDuration,int cookExpertise,String cookType, int nbOfPerson) throws SQLException{
		List<Recipe> l = new ArrayList<Recipe>();
		
		PreparedStatement ps =this.db.prepareStatement("SELECT * FROM receipes WHERE preparationduration=? AND cookexpertise=? AND cooktype=? AND nbofperson=?");
		ps.setInt(1, preparationDuration);
		ps.setInt(2, cookExpertise);
		ps.setString(3, cookType);
		ps.setInt(4, nbOfPerson);
		
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Recipe r =new Recipe(rs.getInt("idreceipes"), rs.getString("title"), rs.getString("sumup"), rs.getString("description"),
					rs.getString("imageref"), rs.getInt("nbofperson"), rs.getString("cooktype"), rs.getInt("cookexpertise"), 
					rs.getInt("preparationduration"));
			l.add(r);
		}
		
		rs.close();
		ps.close();
		
		return l;
	}
	
	public List<String> getAllCookType() throws SQLException{
		List<String> l = new ArrayList<String>();
		
		PreparedStatement ps =this.db.prepareStatement("SELECT distinct(cooktype) FROM receipes");
		
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()){
			l.add(rs.getString("cooktype"));
		}
		
		rs.close();
		ps.close();
		
		return l;
	}
	
	public boolean recipeExists(Integer id) throws SQLException {
		PreparedStatement ps =this.db.prepareStatement("SELECT * FROM receipes WHERE idreceipes= ?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			rs.close();
			ps.close();
			return true;
		}
		
		rs.close();
		ps.close();
		return false;
	}

}
